package CelestialDataAccess;

import javax.sql.rowset.*; // provides cached row set interface
import java.sql.*;         // provides connection object and sql exception
import java.util.Dictionary;
import java.util.List;
import java.util.ArrayList; // for test routine only
import java.util.Iterator;  // for test routine only

import CelestialDataStructures.*;

public class CustomersDataAccess extends DataAccessBase{

    public static final String CUST_ID = "CustomerID";
    public static final String PASSWORD = "Password";
    public static final String USR_LEVEL = "UserLevel";
    public static final String REG_ON = "RegisteredOn";

    public static final String FIRST_NAME = "FirstName";
    public static final String LAST_NAME = "LastName";
    public static final String EMAIL = "Email";
    public static final String CONTACT = "Contact";
    public static final String DOB = "DOB";

    public static final String COUNTRY = "Country";
    public static final String CITY = "City";
    public static final String STATE = "State";
    public static final String ADDR_L1 = "AddressLine1";
    public static final String ADDR_L2 = "AddressLine2";
    public static final String POSTAL = "PostalCode";

    public static final String CC_NUM = "CreditCardNumber";
    public static final String CC_CVV = "CreditCardCVV";
    public static final String CC_EXPIRY = "CreditCardExpiry";
    public static final String CC_HOLDER = "CreditCardHolder";
    public static final String CC_TYPE = "CreditCardType";

    public static final String IS_ACTIVE = "IsActive";

    /*************************************************************************/
    /* Get/Set methods */
    public SearchCriteria getSearchCriteria(){
        return new CustomersSearchCriteria();
    }
    /*************************************************************************/
    /* Client methods */
    public int insertCustomer(Customer cust) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspInsertCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        try{
            stm.setBytes(PASSWORD, CryptService.encrypt(cust.getPassword()));
            stm.setInt(USR_LEVEL, cust.getUserLevel());
            stm.setDate(REG_ON, java.sql.Date.valueOf(DateFormat.format(cust.getRegistrationDate())));

            stm.setString(FIRST_NAME, cust.getFirstName());
            stm.setString(LAST_NAME, cust.getLastName());
            stm.setString(EMAIL, cust.getEmail());
            stm.setString(CONTACT, cust.getContact());
            stm.setDate(DOB, java.sql.Date.valueOf(DateFormat.format(cust.getDOB())));

            stm.setString(COUNTRY, cust.getCountry());
            stm.setString(CITY, cust.getCity());
            stm.setString(STATE, cust.getState());
            stm.setString(ADDR_L1, cust.getAddressLine1());
            stm.setString(ADDR_L2, cust.getAddressLine2());
            stm.setString(POSTAL, cust.getPostalCode());
            
            stm.setString(CC_NUM, cust.getCreditCardNumber());
            stm.setString(CC_CVV, cust.getCreditCardCVV());
            stm.setString(CC_HOLDER, cust.getCreditCardHolder());
            stm.setInt(CC_TYPE, cust.getCreditCardType());
            stm.setDate(CC_EXPIRY, java.sql.Date.valueOf(DateFormat.format(cust.getCreditCardExpiry())));
            
            stm.setBoolean(IS_ACTIVE, cust.isActive());

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }catch(Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public int updateCustomer(Customer cust) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspUpdateCustomer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        try{
            stm.setInt(CUST_ID, cust.getCustomerID());
            stm.setBytes(PASSWORD, CryptService.encrypt(cust.getPassword()));
            stm.setInt(USR_LEVEL, cust.getUserLevel());
            stm.setDate(REG_ON, java.sql.Date.valueOf(DateFormat.format(cust.getRegistrationDate())));

            stm.setString(FIRST_NAME, cust.getFirstName());
            stm.setString(LAST_NAME, cust.getLastName());
            stm.setString(EMAIL, cust.getEmail());
            stm.setString(CONTACT, cust.getContact());
            stm.setDate(DOB, java.sql.Date.valueOf(DateFormat.format(cust.getDOB())));

            stm.setString(COUNTRY, cust.getCountry());
            stm.setString(CITY, cust.getCity());
            stm.setString(STATE, cust.getState());
            stm.setString(ADDR_L1, cust.getAddressLine1());
            stm.setString(ADDR_L2, cust.getAddressLine2());
            stm.setString(POSTAL, cust.getPostalCode());

            stm.setString(CC_NUM, cust.getCreditCardNumber());
            stm.setString(CC_CVV, cust.getCreditCardCVV());
            stm.setString(CC_HOLDER, cust.getCreditCardHolder());
            stm.setInt(CC_TYPE, cust.getCreditCardType());
            stm.setDate(CC_EXPIRY, java.sql.Date.valueOf(DateFormat.format(cust.getCreditCardExpiry())));

            stm.setBoolean(IS_ACTIVE, cust.isActive());

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }catch(Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
         return 1;
    }
    /*----------------------------------------------------------------------*/
    public int deleteCustomer(int cust_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspDeleteCustomer(?)}");
        try{
            stm.setInt(CUST_ID,  cust_id);
            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public Customer getCustomerByID(int cust_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetCustomerByID(?)}");
        try{
            stm.setInt(CUST_ID, cust_id);

            CachedRowSet crs = getData(stm);
            stm.close();

            crs.last();
            if(crs.getRow() < 1)
                return null;

            return Customer.createCustomer(crs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public Customer getCustomerByEmail(String email) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetCustomerByEmail(?)}");
        try{
            stm.setString(EMAIL, email);

            CachedRowSet crs = getData(stm);
            stm.close();

            crs.last();
            if(crs.getRow() < 1)
                return null;

            return Customer.createCustomer(crs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public int getNextCustomerID() throws DataAccessException{
    
        CallableStatement stm = prepareStatement("{CALL uspGetNextCustomerID}");
        CachedRowSet crs = getData(stm);
        try{
            stm.close();
            crs.last();
            return crs.getInt(1);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public int activate(String email) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspActivateCustomer(?)}");
      
        try{
            stm.setString(EMAIL, email);
            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public void fill(Dictionary<Integer, Customer> dic) throws DataAccessException{

       CallableStatement stm = prepareStatement("{CALL uspGetCustomers()}");
       DataAccessBase.fill(Customer.getFactory(), dic, stm);
       try{
           stm.close();
       }catch(SQLException ex){
           throw new DataAccessException(ex.getMessage());
       }
    }
    /*----------------------------------------------------------------------*/
    public void fill(List<Customer> lst) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetCustomers()}");
        DataAccessBase.fill(Customer.getFactory(), lst, stm);
        try{
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    public void searchCustomers(SearchCriteria criteria, List<Customer> lst) throws DataAccessException{

        /* don't build search string if "non-customer" criteria is passed */
        if(criteria.getClass() != CustomersSearchCriteria.class) return;

        CallableStatement stm = prepareStatement(criteria.buildSQL());
        DataAccessBase.fill(Customer.getFactory(), lst, stm);
        try{
           stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*----------------------------------------------------------------------*/
    void searchCustomers(String search_sql, List<Customer> lst) throws DataAccessException{

        CallableStatement stm = prepareStatement(search_sql);
        DataAccessBase.fill(Customer.getFactory(), lst, stm);
        try{
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /************************************************************************/
    /* Helpers */

    /************************************************************************/
    /* Test */
    public static void main(String[] args){
        
        Customer cust;
        CustomersDataAccess daCustomers;

        try{
            daCustomers = new CustomersDataAccess();

            /* test get next customer id */
            //System.out.println(daCustomers.getCustomerByEmail("chseng.acad@gmail.com").isActive());

            /* test search */
           List<Customer> lst = new ArrayList<Customer>();

           SearchCriteria criteria = daCustomers.getSearchCriteria();
           criteria.setValue("UserLevel", "1");
           criteria.setValue("LastName", "Wang");
           criteria.setValue("FirstName", "Hua Seng");
           criteria.setOperator("UserLevel", SearchCriteria.EQU);
           criteria.setConnective(SearchCriteria.OR);

           daCustomers.searchCustomers(criteria, lst);
           Iterator<Customer> cust_itr = lst.iterator();
           while(cust_itr.hasNext()){
               cust = cust_itr.next();
                System.out.println(cust.getFirstName() + "\t" +
                        cust.getPassword() + "\t" + cust.getUserLevel() +"\t" +
                        DateFormat.format(cust.getRegistrationDate()).toString() + "\t" + cust.getCustomerID() + "\t" + cust.getEmail());
                cust.setPassword("1123581321");
                //daCustomers.updateCustomer(cust);
                //System.out.println(cust.toString(true));
           }
 
           cust = daCustomers.getCustomerByID(100000);
           cust.setPassword("Romance111");
           cust.setCreditCardCVV("123");
           cust.setCountry("SG");
           cust.setState("Singapore");

           cust.setCreditCardNumber("5548274033207951");
           cust.setCreditCardExpiry(DateFormat.parse("2012-12-01"));
           cust.setCreditCardType(CreditCardType.MASTER);
           cust.setAddressLine1("Blk 23 Sin Ming Road");
           cust.setAddressLine2("#02-03");
           cust.setPostalCode("570023");
           cust.setEmail("chseng@homecodeworks.com");
           cust.setContact("65 9730 7364");
           daCustomers.updateCustomer(cust);

           cust = daCustomers.getCustomerByID(100000);
           System.out.println(cust.toString(true));

           DataAccessBase.close();

        }catch(Exception ex){
            System.out.println(ex.getMessage());
            System.out.println(ex.toString());
            return;
        }
    }
    /************************************************************************/
    /* Search Criteria Class */
    private class CustomersSearchCriteria extends SearchCriteria{

        /*********************************************************************/
        /* Constructors */
        CustomersSearchCriteria(){

            m_strSelectClause = "SELECT * FROM tblCustomers";

            m_mapValues.put(CUST_ID, null);
            m_mapValues.put(EMAIL, null);
            m_mapValues.put(FIRST_NAME, null);
            m_mapValues.put(LAST_NAME, null);
            m_mapValues.put(IS_ACTIVE, null);
            m_mapValues.put(REG_ON, null);
            m_mapValues.put(USR_LEVEL, null);

            m_mapOperators.put(CUST_ID, SearchCriteria.EQU);
            m_mapOperators.put(EMAIL, SearchCriteria.EQU);
            m_mapOperators.put(FIRST_NAME, SearchCriteria.EQU);
            m_mapOperators.put(LAST_NAME, SearchCriteria.EQU);
            m_mapOperators.put(IS_ACTIVE, SearchCriteria.EQU);
            m_mapOperators.put(REG_ON, SearchCriteria.EQU);
            m_mapOperators.put(USR_LEVEL, SearchCriteria.EQU);
        }
        /*********************************************************************/
    }
    /************************************************************************/
}
